package main.java.jdbc11;

import java.sql.*;
import java.util.Scanner;

/**
 * @Auther:Liu
 * @Testname:jdbcStudent
 * @Date:2025/3/11 9:54
 */
public class jdbcStudent {
    static boolean flag = true;
    final String url = "jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=UTC";
    final String user = "root";
    final String password = "123456";

    public static void main(String[] args) throws Exception {
        jdbcStudent jdbcStudent = new jdbcStudent();
        Scanner scanner = new Scanner(System.in);
        while (flag) {
            System.out.println("——————————————————");
            System.out.println("栏     目     表");
            System.out.println("——————————————————");
            System.out.println("| 1.查  询  对  象 |");
            System.out.println("| 2.插  入  对  象 |");
            System.out.println("| 3.更  新  对  象 |");
            System.out.println("| 4.删  除  对  象 |");
            System.out.println("| 5.登         录 |");
            System.out.println("| 6.退         出 |");
            System.out.println("——————————————————");
            System.out.println("请输入选择:");
            int i = scanner.nextInt();
            switch (i) {
                case 1: {
                    System.out.println("请输入查询类型:(One or All)");
                    int key = scanner.nextInt();
                    if (key == 1) {
                        System.out.println("请输入要查询信息的id:");
                        int id11 = scanner.nextInt();
                        jdbcStudent.findOne(id11);
                        break;
                    } else {
                        jdbcStudent.findAll();
                        break;
                    }
                }
                case 2: {
                    System.out.println("请输入要插入信息的name:");
                    String name2 = scanner.next();
                    System.out.println("请输入要插入信息的age:");
                    int age2 = scanner.nextInt();
                    System.out.println("请输入要插入信息的sex:");
                    String sex2 = scanner.next();
                    System.out.println("请输入要插入信息的phone:");
                    int phone2 = scanner.nextInt();
                    System.out.println("请输入要插入信息的area:");
                    String area2 = scanner.next();
                    jdbcStudent.insert(name2, age2, sex2, phone2, area2);
                    break;
                }
                case 3: {
                    System.out.println("请输入要更新信息的id:");
                    int id = scanner.nextInt();
                    System.out.println("请输入要更新信息的name:");
                    String name = scanner.next();
                    System.out.println("请输入要更新信息的age:");
                    int age = scanner.nextInt();
                    System.out.println("请输入要更新信息的sex:");
                    String sex = scanner.next();
                    System.out.println("请输入要更新信息的phone:");
                    int phone = scanner.nextInt();
                    System.out.println("请输入要更新信息的area:");
                    String area = scanner.next();
                    jdbcStudent.update(id, name, age, sex, phone, area);
                    break;
                }
                case 4: {
                    System.out.println("请输入要删除信息的id:");
                    int id4 = scanner.nextInt();
                    jdbcStudent.delete(id4);
                    break;
                }
                case 5: {
                    System.out.println("请输入admire:");
                    String admire = scanner.next();
                    System.out.println("请输入password:");
                    int password = scanner.nextInt();
                    boolean search = jdbcStudent.Search(admire, password);
                    if (search) {
                        System.out.println("登陆成功");
                    } else {
                        System.out.println("登录失败");
                    }
                    break;
                }
                case 6: {
                    exit();
                    break;
                }
            }
        }
    }

    //退出
    public static void exit() {
        flag = false;
    }

    //连接
    public Connection Link() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //创建对象
    public int state(String sql, Connection conn) {
        int i = 0;
        Statement statement = null;
        try {
            statement = conn.createStatement();
            i = statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    //创建对象（预编译）
    public int state1(String sql, Connection conn) {
        int i = 0;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;

    }

    //查询(单个)
    public void findOne(int id) throws Exception {
        Connection conn = Link();
        //3.定义查询语句
        String sql = "select * from stu_student where stu_id='" + id + "'";
        //4.获取查询对象
        Statement statement = conn.createStatement();
        //5.实用对象执行数据
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            int stu_id = resultSet.getInt("stu_id");
            String stu_name = resultSet.getString("stu_name");
            int stu_age = resultSet.getInt("stu_age");
            String stu_sex = resultSet.getString("stu_sex");
            int stu_phone = resultSet.getInt("stu_phone");
            String stu_address = resultSet.getString("stu_address");
            System.out.println(stu_id + "||" + stu_name + "||" + stu_age + "||" + stu_sex + "||" + stu_phone + "||" + stu_address);

        }
        //7.释放
        statement.close();
        conn.close();
    }

    //查询(多个)
    public void findAll() throws Exception {
        Connection conn = Link();
        //3.定义查询语句
        String sql = "select * from stu_student";
        //4.获取查询对象
        Statement statement = conn.createStatement();
        //5.实用对象执行数据
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            int stu_id = resultSet.getInt("stu_id");
            String stu_name = resultSet.getString("stu_name");
            int stu_age = resultSet.getInt("stu_age");
            String stu_sex = resultSet.getString("stu_sex");
            int stu_phone = resultSet.getInt("stu_phone");
            String stu_address = resultSet.getString("stu_address");
            System.out.println(stu_id + "||" + stu_name + "||" + stu_age + "||" + stu_sex + "||" + stu_phone + "||" + stu_address);
        }
        //7.释放
        statement.close();
        conn.close();
    }

    //插入
    public void insert(String name2, int age2, String sex2, int phone2, String area2) throws Exception {
        Connection conn = Link();
        String sql = "insert into stu_student values(null,'" + name2 + "','" + age2 + "','" + sex2 + "','" + phone2 + "','" + area2 + "')";
        int i = state1(sql, conn);
        if (i != 0) {
            System.out.println("插入成功");
        } else {
            System.out.println("插入失败");
        }
    }

    //更新
    public void update(int id, String name, int age, String sex, int phone, String area) throws Exception {
        Connection conn = Link();
        String sql = "update stu_student set stu_name='" + name + "',stu_age='" + age + "',stu_sex='" + sex + "',stu_phone='" + phone + "',stu_address='" + area + "' where stu_id='" + id + "'";
        int i = state(sql, conn);
        if (i != 0) {
            System.out.println("更新成功");
        } else {
            System.out.println("更新失败，对象不存在");
        }
    }

    //删除
    public void delete(int id) throws Exception {
        Connection conn = Link();
        String sql = "delete from stu_student where stu_id='" + id + "'";
        int i = state(sql, conn);
        if (i != 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
    }

    //检验管理员
    public boolean Search(String admire1, int password1) throws SQLException {
        Connection conn = Link();
        String sql = "select * from manage where admire='" + admire1 + "'and password='" + password1 + "'";
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        return resultSet.next();
    }
}
